package com.ildepinc.persistence.abstracts;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LongSummaryStatistics;
import java.util.stream.LongStream;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

import com.ildepinc.persistence.enums.NewProgressiveMethod;
import com.ildepinc.persistence.interfaces.DaoCommonBehaviour;
import com.ildepinc.persistence.interfaces.DaoWithSequenceBehaviour;
import com.ildepinc.persistence.interfaces.JdbcConnectionBehaviour;
import com.ildepinc.persistence.interfaces.SequenceGenericBehaviour;

public abstract class DaoCommon implements DaoCommonBehaviour, DaoWithSequenceBehaviour {
  private String sqlCommand;

  private ArrayList<Object> sqlParameters;
  private JdbcConnectionBehaviour jdbcConnection;
  private SequenceGenericBehaviour sequence;

  @Override
  public PreparedStatement buildStatement() throws Exception {
    PreparedStatement statement = null;
    Object currentParameter;
    statement = this.jdbcConnection.doConnect().prepareStatement(this.sqlCommand, ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    if ((this.sqlParameters != null) && (!this.sqlParameters.isEmpty())) {
      for (int i = 0; i < this.sqlParameters.size(); i++) {
        currentParameter = this.sqlParameters.get(i);
        if (currentParameter instanceof Boolean) {
          statement.setBoolean(i + 1, (Boolean) currentParameter);
        }
        if (currentParameter instanceof String) {
          statement.setString(i + 1, currentParameter.toString());
        }
        if (currentParameter instanceof Integer) {
          statement.setInt(i + 1, (Integer) currentParameter);
        }
        if (currentParameter instanceof Long) {
          statement.setLong(i + 1, (Long) currentParameter);
        }
        if (currentParameter instanceof Double) {
          statement.setDouble(i + 1, (Double) currentParameter);
        }
        if (currentParameter instanceof Float) {
          statement.setFloat(i + 1, (Float) currentParameter);
        }
        if (currentParameter instanceof BigDecimal) {
          statement.setBigDecimal(i + 1, (BigDecimal) currentParameter);
        }
        if (currentParameter instanceof Date) {
          statement.setDate(i + 1, (Date) currentParameter);
        }
        if (currentParameter instanceof Timestamp) {
          statement.setTimestamp(i + 1, (Timestamp) currentParameter);
        }
      }
    }
    return statement;
  }

  @Override
  public Integer doManualDmlOrDdl() throws Exception {
    Integer numberOfEffected = null;
    numberOfEffected = this.buildStatement().executeUpdate();
    return numberOfEffected;
  }

  @Override
  public ResultSet doManualSelect() throws Exception {
    ResultSet resultSet = null;
    resultSet = this.buildStatement().executeQuery();
    return resultSet;
  }

  @Override
  public JdbcConnectionBehaviour getJdbcConnection() {
    return this.jdbcConnection;
  }

  @Override
  public Long getNewProgressive(NewProgressiveMethod newProgressiveMethod, String schemaName, String tableName,
      String columnName) throws Exception {
    Long newProgressive = 0L;
    this.setSqlCommand("SELECT " + columnName + " FROM " + schemaName + "." + tableName + " ORDER BY ID");
    long[] ids = new QueryRunner()
        .query(this.getJdbcConnection().getConnection(), this.getSqlCommand(), new ArrayListHandler()).stream()
        .mapToLong(array -> Long.valueOf(array[0].toString())).toArray();
    LongSummaryStatistics summaryStatistics = Arrays.stream(ids).summaryStatistics();
    if (newProgressiveMethod.equals(NewProgressiveMethod.FIRST_AVAILABLE)) {
      // https://stackoverflow.com/questions/61384967/get-missing-number-from-int-array-with-java-streams
      long expectedSum = LongStream.rangeClosed(summaryStatistics.getMin(), summaryStatistics.getMax()).sum();
      long actualSum = (long) summaryStatistics.getSum();
      newProgressive = expectedSum - actualSum;
      if (newProgressive.equals(0L)) {
        newProgressive++;
      }
    }
    if (newProgressiveMethod.equals(NewProgressiveMethod.NEXT_AVAILABLE)) {
      if (summaryStatistics.getCount() > 0) {
        newProgressive = summaryStatistics.getMax() + 1;
      } else {
        newProgressive++;
      }
    }
    if (newProgressiveMethod.equals(NewProgressiveMethod.NEXT_IN_SEQUENCE)) {
      newProgressive = this.getNextVal();
    }
    return newProgressive;
  }

  @Override
  public String getSqlCommand() {
    return this.sqlCommand;
  }

  @Override
  public ArrayList<Object> getSqlParameters() {
    return this.sqlParameters;
  }

  @Override
  public void setJdbcConnection(JdbcConnectionBehaviour connection) {
    this.jdbcConnection = connection;
  }

  @Override
  public void setSqlCommand(String sqlCommand) {
    this.sqlCommand = sqlCommand;
  }

  @Override
  public void setSqlParameters(ArrayList<Object> sqlParameters) {
    this.sqlParameters = sqlParameters;
  }

  @Override
  public void clear() {
    this.sqlCommand = null;
    this.sqlParameters = null;
    this.jdbcConnection = null;
  }

  private Long getFromSequence(Boolean isNextVal) throws Exception {
    Long nextVal = null;
    ResultSet resultSet = null;
    String sqlCommand = null;
    if(isNextVal) {
      sqlCommand = this.sequence.getSqlNextVal();
    }
    else {
      sqlCommand = this.sequence.getSqlNextVal();
    }
    this.sqlParameters.clear();
    this.setSqlCommand(sqlCommand);
    resultSet = this.doManualSelect();
    resultSet.first();
    nextVal = resultSet.getLong(1);
    return nextVal;
  }

  @Override
  public Long getCurrVal() throws Exception {
    return this.getFromSequence(false);
  }

  @Override
  public Long getNextVal() throws Exception {
    return this.getFromSequence(true);
  }

  @Override
  public SequenceGenericBehaviour getSequence() {
    return this.sequence;
  }

  @Override
  public void setSequence(SequenceGenericBehaviour sequence) {
    this.sequence = sequence;
  }
}
